﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;

namespace ZLDC.DAL.Estate
{
                public class ReportDAL
    {
                                                                public static DataTable p_report_dept_list_table(int typeid, string condition, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@typeid", typeid));
            parms.Add(new SqlParameter("@condition", condition));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_report_dept_list", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
       
                                                                        public static DataTable p_rank_list_table(int typeid, string condition, string orderbyname, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@typeid", typeid));
            parms.Add(new SqlParameter("@condition", condition));
            parms.Add(new SqlParameter("@orderbyname", orderbyname));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_rank_list", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable GetReportCountByWeek(string feild, string condition, _Database db)
        {
            DataTable dt = new DataTable();

            try
            {
                string strSql = "set DATEFIRST 1; select datename(week,countDate),convert(varchar(10),CAST(countDate as datetime)-(datepart(weekday,countDate)-1),120),convert(varchar(10),CAST(countDate as datetime)+(7-datepart(weekday,countDate)),120) , sum(" + feild + ") from t_quantification ";
                strSql += condition;
                strSql += " group by datename(week,countDate),  convert(varchar(10),CAST(countDate as datetime)-(datepart(weekday,countDate)-1),120),convert(varchar(10),CAST(countDate as datetime)+(7-datepart(weekday,countDate)),120)";
                DataSet ds = db.Query(strSql);
                dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

        public static DataTable GetReportCountByMonth(string feild, string condition, _Database db)
        {
            DataTable dt = new DataTable();

            try
            {
                string strSql = "select  CONVERT(varchar(7),countDate, 120)  , sum(" + feild + ") from t_quantification ";
                strSql += condition;
                strSql += " group by CONVERT(varchar(7),countDate, 120)";
                DataSet ds = db.Query(strSql);
                dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

        public static DataTable GetYeji(string userEHRId, string deptid, string fgs, string beginDate, string endDate, _Database db)
        {
            DataTable dt = new DataTable();

            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendLine("select kfb.f_get_yejiinfo('" + userEHRId + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '个人业绩') ");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + deptid + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '部门业绩')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + userEHRId + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '个人租单单数')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + userEHRId + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '个人租单业绩')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + userEHRId + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '个人卖单单数')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + userEHRId + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '个人卖单业绩')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + deptid + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '部门租单单数')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + deptid + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '部门租单业绩')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + deptid + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '部门卖单单数')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + deptid + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '部门卖单业绩')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + userEHRId + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '个人排名')");
                strSql.AppendLine(",kfb.f_get_yejiinfo('" + deptid + "','" + fgs + "', '" + beginDate + "','" + endDate + "', '部门排名')");
                DataSet ds = db.Query(strSql.ToString());
                dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;

        }

                                                                                public static DataSet GetYejiAnalysis(string beginDate, string endDate, string fgs, int dtype, int deptid, _Database db)
        {
            DataSet ds = new DataSet();

            try
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@bDate", beginDate));
                parms.Add(new SqlParameter("@eDate", endDate));
                parms.Add(new SqlParameter("@deptId", deptid));
                parms.Add(new SqlParameter("@fgs", fgs));
                parms.Add(new SqlParameter("@dtype", dtype));
                ds = db.GetDataSet("P_stats_GetQut_song", parms.ToArray());
            }
            catch { }
            finally { db.close(); }

            return ds;
        }

        #region 量化详情

                                                                                        public static DataTable p_quantificationlog_type_table(int typeid, int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@typeid", typeid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            DataTable dt = null;
            dt = db.GetDataTable("p_quantificationlog_type", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            return dt;
        }

        #endregion
    }
}
